<?php
// +----------------------------------------------------------------------
// | Scallion 
// +----------------------------------------------------------------------
// | Copyright (c) 2018 http://www.scallion.top All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: Liuxg < 420695648@qq.com>
// +----------------------------------------------------------------------
namespace app\admin\controller;

use cmf\controller\AdminBaseController;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;

class StudentController extends AdminBaseController
{

    /**
     * 学生管理
     */
    public function index()
    {
		if(isset($_COOKIE['hold_student'])){
			$hold_student_data = db('student')->alias('a')->where(array("a.id"=>array("in",$_COOKIE['hold_student'])))->join('user w','a.buildid = w.id')->field('a.id as id,a.name as name,a.IDcard as IDcard,a.birthday as birthday,a.sex as sex,a.deg as deg,a.phone as phone,a.phone2 as phone2,a.note as note,a.buildtime as buildtime,w.user_nickname as build_name,a.onion as onion')->select();
			$not_in = array("a.id"=>array("not in",$_COOKIE['hold_student']));
			$this->assign("hold_student",$hold_student_data);
		}
		else{
			$not_in = false;
		}
		$student_info = db('student')->alias('a')->where($not_in)->join('user w','a.buildid = w.id')->order('buildtime desc')->field('a.id as id,a.name as name,a.IDcard as IDcard,a.birthday as birthday,a.sex as sex,a.deg as deg,a.phone as phone,a.phone2 as phone2,a.note as note,a.buildtime as buildtime,w.user_nickname as build_name,a.onion as onion')->paginate(10);
		$this->assign('page', $student_info->render());
        $this->assign("student_info", $student_info);
        return $this->fetch();
    }
	
	/**
     * 学生查询
     */
	public function find_student(){
		if(isset($_COOKIE['hold_student'])){
			$hold_student_data = db('student')->alias('a')->where(array("a.id"=>array("in",$_COOKIE['hold_student'])))->join('user w','a.buildid = w.id')->field('a.id as id,a.name as name,a.IDcard as IDcard,a.birthday as birthday,a.sex as sex,a.deg as deg,a.phone as phone,a.phone2 as phone2,a.note as note,a.buildtime as buildtime,w.user_nickname as build_name,a.onion as onion')->select();
			$this->assign("hold_student",$hold_student_data);
		}
		
		$param = $this->request->param();
		if(!isset($param['name'])){$param['name']='';}
		if(!isset($param['phone'])){$param['phone']='';}
		if(!isset($param['deg'])){
			$param['deg']='';
		}
		if(isset($_COOKIE['hold_student'])&&$_COOKIE['hold_student']!=null){
			$not_in = array("a.id"=>array("not in",$_COOKIE['hold_student']));
		}
		else{
			$not_in = false;
		}
		if($param['deg']!=''){
			$deg = date('m')>8 ? date('Y') - $param['deg'] + 1 : date('Y') - $param['deg'];
			$result = db('student')->where('deg',$deg)
			->alias('a')
			->join('user w','a.buildid = w.id')
			->where('name','like','%'.$param['name'].'%')
			->where('phone|phone2','like','%'.$param['phone'].'%')
			->where($not_in)
			->field('a.id as id,a.name as name,a.IDcard as IDcard,a.birthday as birthday,a.sex as sex,a.deg as deg,a.phone as phone,a.phone2 as phone2,a.note as note,a.buildtime as buildtime,w.user_nickname as build_name,a.onion as onion')
			->order('buildtime desc')->limit('10')->select();
		}
		else{
			$result = db('student')->where('name','like','%'.$param['name'].'%')
			->alias('a')
			->join('user w','a.buildid = w.id')
			->where('phone|phone2','like','%'.$param['phone'].'%')
			->where($not_in)
			->field('a.id as id,a.name as name,a.IDcard as IDcard,a.birthday as birthday,a.sex as sex,a.deg as deg,a.phone as phone,a.phone2 as phone2,a.note as note,a.buildtime as buildtime,w.user_nickname as build_name,a.onion as onion')
			->order('buildtime desc')->limit('10')->select();
		}
		
		$this->assign("student_info", $result);
		$this->assign("param", $param);
		return $this->fetch('index');
	}
	
	/**
     * 自动补全
     */
	public function find_student_name(){
		$param = $this->request->param();
		$result = db('student')->field('name,phone')->where('name','like','%'.$param['name'].'%')->limit('5')->select();
		return json_encode($result);
	}
	
	public function find_student_phone(){
		$param = $this->request->param();
		$result1 = db('student')->field('name,phone')->where('phone','like','%'.$param['phone'].'%')->limit('5')->select();
		$i = count($result1);
		if($i<5){
			$result2 = db('student')->field('name,phone2')->where('phone2','like','%'.$param['phone'].'%')->limit('5')->select();			
			$j = count($result2);
			$result = $result1;
			for($k=0;$k+$i<($i+$j<5 ? $i+$j : 5);$k++){
				$result[$k+$i] = $result2[$k];
			}
		}
		else{
			$result = $result1;
		}
		return str_replace("phone2","phone",json_encode($result));
	}
	
	/**
     * 新增/编辑学生
     */
	public function add_student(){
		if ($this->request->isGet()) {
			$param = $this->request->param();
			$this->assign("param", $param);
		}
		return $this->fetch();
	}
	
	public function edit_student(){
		if($this->request->isGet()){
			$param = $this->request->param();
			$result = db('student')->where('id='.$param['id'])->find();
			$result['deg'] = date('m')>8 ? date('Y')-$result['deg']+1 : date('Y')-$result['deg'];
			// dump($result);
			$this->assign("param", $result);
			return $this->fetch();
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function add_student_Post(){
		if ($this->request->isPost()){
			$param = $this->request->param();
			$result = $this->validate($param, 'Student');
            if ($result !== true) {
                $this->error($result);
            }
			else
			{
				//学生数据整理
				$param['deg'] =  date('m')>8 ? date('Y') - $param['deg'] + 1 : date('Y') - $param['deg'];
				$param['birthday'] = date("Y-m-d",strtotime($param['birthday']));
				$param['buildtime'] = time();
				$param['buildid'] = cmf_get_current_admin_id();
				$res = db('student')->insert($param);
				if($res){
					
					//填写日志
					$action = '新增学生';
					$json_data = json_encode($param);
					$result = '成功';
					self::data_log($action,$json_data,$result);
					
					$this->success('添加学生成功',url('Student/find_student',['deg'=>'','name'=>$param['name'],'phone'=>$param['phone']]));
				}
				else{
					$this->error('系统错误！errcode：-101');
				}
			}
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function edit_student_Post(){
		if ($this->request->isPost()){
			$param = $this->request->param();
			
			//学生是否存在
			$is_exist = db('student')->where('id='.$param['id'])->find();
			if(!$is_exist){
				$this->error('学生不存在！');
			}
			// 验证基本信息规则
			$result = $this->validate($param, 'Student');
            if ($result !== true) {
                $this->error($result);
            }
			else
			{
				//学生数据整理
				$param['deg'] = date('m')>8 ? date('Y') - $param['deg'] + 1 : date('Y') - $param['deg'];
				$param['birthday'] = date("Y-m-d",strtotime($param['birthday']));
				$param['buildtime'] = time();
				$res = db('student')->where('id='.$param['id'])->update($param);
				if($res){
					
					//填写日志
					$action = '编辑学生';
					$json_data = json_encode($param);
					$result = '成功';
					$json_old_data = json_encode($is_exist);
					self::data_log($action,$json_data,$result,$json_old_data);
					
					$this->success('保存学生资料成功',url('Student/find_student',['deg'=>'','name'=>$param['name'],'phone'=>$param['phone']]));
				}
				else{
					$this->error('系统错误！errcode：-102');
				}
			}
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function delete_student(){
		if ($this->request->isPost()){
			$ids = $this->request->param();
			$operate_log = '';
			if(count($ids)<1){
				$this->error('请选择要删除的学生！');
			}
			else{
				for($i=0;$i<count($ids['ids']);$i++){
					// 学生是否存在
					$is_exist[$i] = db('student')->where('id='.$ids['ids'][$i])->find();
					$is_sign_in[$i] = db('class_student_sign_in')->where('student_id='.$ids['ids'][$i])->find();
					if(!$is_exist[$i]){
						//删除反馈
						$operate_log = $operate_log . 'ID：'.$ids['ids'][$i].'的学生不存在！</br>';
					}
					elseif($is_sign_in[$i]){
						$operate_log = $operate_log . 'ID：'.$ids['ids'][$i].'的学生已有报班记录，不能删除！</br>';
					}
					else{
						//删除操作
						//填写日志
						$action = '删除学生'.$is_exist[$i]['name'].'ID为：'.$is_exist[$i]['id'];
						$json_data = json_encode($is_exist[$i]);
						$result = '成功';
						$first = self::data_log($action,$json_data,$result);
						if(!$first){$this->error('记录日志错误！ -103');}
						//放入回收站
						$second = self::data_recycle('student',$json_data);
						if(!$second){$this->error('数据回收错误！ -104');}
						//删除student表中数据
						$third = db('student')->where('id='.$ids['ids'][$i])->delete();
						//删除反馈
						if($first && $second && $third){
							$operate_log = $operate_log . 'ID：'.$is_exist[$i]['id'].',姓名：'.$is_exist[$i]['name'].'删除成功！</br>';
						}
						else{
							$this->error('未知错误！ -105');
						}
					}
				}
				
				$this->success($operate_log,url('Student/index'));
			}
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function import_student(){
		return $this->fetch();
	}
	
	public function import_student_post(){
		if ($this->request->isPost()){
			$param = $this->request->param();
			$file_types = explode(".",$param['file_name']);
			$file_type = $file_types[count($file_types)-1];
			/*判别是不是.xls文件，判别是不是excel文件*/
			if(strtolower($file_type)!="xls"&&strtolower($file_type)!="xlsx")
			{
				$this->error('不是Excel文件，请重新上传');
			}
			else{
				$res = $this->ExcelToArrary('upload/'.$param['file_url'],$file_type);
				if($res){
					foreach($res as $key_row=>$row){
						foreach($row as $key_col=>$col){
							$value = $this->trimall($col);
							if(!isset($index)||!$this->is_index($index)||$index['index']==$key_row){
								$index['index'] = $key_row;
								switch($value){
									case '学生姓名': 
									case '姓名':
										$index['name'] = $key_col;
										break;
									case '年级':
									case '入学年份':
										$index['deg'] = $key_col;
										break;
									case '电话':
									case '联系方式':
									case '手机':
									case '第一联系电话':
									case '联系电话':
									case '家长电话':
									case '家长手机':
										$index['phone'] = $key_col;
										break;
									case '第二联系方式':
									case '第二联系电话':
									case '备用联系电话':
									case '备用联系方式':
									case '备用电话':
									case '备用手机':
										$index['phone2'] = $key_col;
										break;
									case '性别':
										$index['sex'] = $key_col;
										break;
								}
							}
							else if($this->is_index($index)){
								switch($key_col){
									case $index['name']:
										$data[$key_row-$index['index']-1]['name'] = $value;
										break;
									case $index['deg']:
										switch($value){
											case '一年级':
											case '1年级':
											case '1':
											case '一':
											case date('Y'):
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y'):date('Y')-1;
												break;
											case '二年级':
											case '2年级':
											case '2':
											case '二':
											case date('Y')-1:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-1:date('Y')-2;
												break;
											case '三年级':
											case '3年级':
											case '3':
											case '三':
											case date('Y')-2:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-2:date('Y')-3;
												break;
											case '四年级':
											case '4年级':
											case '4':
											case '四':
											case date('Y')-3:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-3:date('Y')-4;
												break;
											case '五年级':
											case '5年级':
											case '5':
											case '五':
											case date('Y')-4:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-4:date('Y')-5;
												break;
											case '六年级':
											case '6年级':
											case '6':
											case '六':
											case date('Y')-5:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-5:date('Y')-6;
												break;
											case '七年级':
											case '7年级':
											case '初一':
											case '初1':
											case '7':
											case '七':
											case date('Y')-6:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-6:date('Y')-7;
												break;
											case '八年级':
											case '8年级':
											case '初二':
											case '初2':
											case '8':
											case '八':
											case date('Y')-7:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-7:date('Y')-8;
												break;
											case '九年级':
											case '9年级':
											case '初三':
											case '初3':
											case '9':
											case '九':
											case date('Y')-8:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-8:date('Y')-9;
												break;
											case '十年级':
											case '10年级':
											case '高一':
											case '高1':
											case '10':
											case '十':
											case date('Y')-9:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-9:date('Y')-10;
												break;
											case '十一年级':
											case '11年级':
											case '高二':
											case '高2':
											case '11':
											case '十一':
											case date('Y')-10:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-10:date('Y')-11;
												break;
											case '十二年级':
											case '12年级':
											case '高三':
											case '高3':
											case '12':
											case '十二':
											case date('Y')-11:
												$data[$key_row-$index['index']-1]['deg'] = date('m')>8 ? date('Y')-11:date('Y')-12;
												break;
										}
										break;
									case $index['phone']:
										if(preg_match("/^((13[0-9])|(14[5,7])|(15[0-3,5-9])|(17[0,3,5-8])|(18[0-9])|166|198|199|(147))\\d{8}$/",$value)){
											$data[$key_row-$index['index']-1]['phone'] = $value;
											break;
										}
										else{
											$this->error('第'.$key_row.'行第'.$key_col.'列数据不正确！请修改后重新上传！');
										}
									default:
										if(isset($index['phone2'])&&$key_col==$index['phone2']){
											$data[$key_row-$index['index']-1]['phone2'] = $value;
										}
										if(isset($index['sex'])&&$key_col==$index['sex']){
											switch($value){
												case '男':
												case '1':
													$data[$key_row-$index['index']-1]['sex'] = '1';
													break;
												case '女':
												case '2':
													$data[$key_row-$index['index']-1]['sex'] = '2';
													break;
												default:
													$data[$key_row-$index['index']-1]['sex'] = '0';
											}
										}
								}
							}
							else{
								$this->error('文件数据错误！请使用模板文件修改后重新上传！');
							}
						}
					}
					if(count($res)<=$index['index']){
						$this->error('必填项数据缺失！请使用模板文件修改后重新上传！');
					}
					// 验证数据
					$validate = validate('student');
					foreach($data as $key=>$vo){
						if(!$validate->check($vo)){
							$this->error('第'.($key+$index['index']+1).'行数据错误：'.$validate->getError());
						}
						$data[$key]['buildtime'] = time();
						$data[$key]['buildid'] = cmf_get_current_admin_id();
						$data[$key]['note'] = '';
					}
					//写入数据
					$import_res = db('student')->insertAll($data);
					if($import_res){
						//填写日志
						$action = '导入学生';
						$json_data = '';
						$result = '成功导入'.$import_res.'条数据！';
						self::data_log($action,$json_data,$result);
						$this->success('成功导入'.$import_res.'条数据！');
					}
					else{
						$this->error('导入失败！');
					}
				}
				else{
					$this->error('导入错误！');
				}
			}
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function ExcelToArrary($filename,$type,$encode='utf-8'){
		if($type=='xls'){
			$objReader = PHPExcel_IOFactory::createReader('Excel5');
		}
		elseif($type=='xlsx'){
			$objReader = PHPExcel_IOFactory::createReader('Excel2007');
		}
		else{
			return false;
		}
		$objReader->setReadDataOnly(true);
		$objPHPExcel = $objReader->load($filename);
		$objWorksheet = $objPHPExcel->getActiveSheet();
		$highestRow = $objWorksheet->getHighestRow();
		$highestColumn = $objWorksheet->getHighestColumn();
		$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
		$excelData = array();
		for ($row = 1; $row <= $highestRow; $row++){
			for ($col = 0; $col < $highestColumnIndex; $col++){
				$excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
			}
		}
		return $excelData;
    }
	
	public function trimall($str)//删除空格
	{
		$oldchar=array(" ","　","\t","\n","\r","	");
		$newchar=array("","","","","","");
		return str_replace($oldchar,$newchar,$str);
	}
	
	public function is_index($index){
		if(!isset($index)||$index==''||$index==null||$index=='undefined'){
			return false;
		}
		else if(!isset($index['name'])||$index['name']==''||$index['name']==null||$index['name']=='undefined'){
			return false;
		}
		else if(!isset($index['deg'])||$index['deg']==''||$index['deg']==null||$index['deg']=='undefined'){
			return false;
		}
		else if(!isset($index['phone'])||$index['phone']==''||$index['phone']==null||$index['phone']=='undefined'){
			return false;
		}
		else{
			return true;
		}
	}
	
	public function student_detail(){
		if($this->request->param()){
			$param = $this->request->param();
			$student = db('student')->where(['id'=>$param['id']])->find();
			$sign_data = db('class_student_sign_in')
			->where(['student_id'=>$param['id']])
			->alias('a')
			->join('class w','a.class_id = w.id')
			->join('teacher t','w.teacher = t.id')
			->join('classroom b','w.classroom_id = b.id')
			->field('a.last_edit_time as last_edit_time,w.name as class_name,t.name as teacher,b.name as classroom')
			->select();
			// 获取课表
			$data = db('class_student_sign_in')
			->alias('s')
			->where(['s.student_id'=>$param['id'],'s.status'=>1])
			->join('class_timetable a','s.class_id = a.class_id')
			->join('teacher t','a.teacher_id = t.id')
			->join('class b','a.class_id = b.id')
			->join('classroom c','a.classroom_id = c.id')
			->join('student_attendance d','d.student_id = s.student_id AND d.timetable_id = a.id','LEFT')
			->field('t.name as teacher,b.name as class_name,c.name as classroom_name,a.class_number as class_number,a.times as times,start_time,end_time,a.id as timetable_id,d.target as target,d.code as code')
			->order('start_time asc')
			->select()->toarray();
			// attendan data
			$attendance_data = db('student_attendance')
			->alias('d')
			->where(['code'=>'2','student_id'=>$param['id']])
			->join('class_timetable a','d.target = a.id')
			->join('teacher t','a.teacher_id = t.id')
			->join('class b','a.class_id = b.id')
			->join('classroom c','a.classroom_id = c.id')
			->field('t.name as teacher,b.name as class_name,c.name as classroom_name,a.class_number as class_number,a.times as times,start_time,end_time,a.id as timetable_id,d.target as target,5 as code')
			->order('start_time asc')
			->select()->toarray();
			// attendan data end
			// dump($attendance_data);exit();
			$assign_data = '';
			$date = '';
			$i = 0;
			$merge_data = array_merge($data,$attendance_data);
			foreach($merge_data as $vo){
				if($date == date('Y-n-j',$vo['start_time'])){
					$i++;
				}
				else{
					$date = date('Y-n-j',$vo['start_time']);
					$i = 0;
				}
				$assign_data[$date][$i]['title'] = '班级：'.$vo['class_name'].'</br>教室：'.$vo['classroom_name'].'</br>第'.$vo['times'].'/'.$vo['class_number'].'次课';
				$assign_data[$date][$i]['startTime'] = date('Y年n月j日 H:i',$vo['start_time']);
				$assign_data[$date][$i]['endTime'] = date('H:i',$vo['end_time']);
				$assign_data[$date][$i]['timetable_id'] = $vo['timetable_id'];
				$assign_data[$date][$i]['teacher'] = $vo['teacher'];
				$assign_data[$date][$i]['code'] = $vo['code'];
				if($vo['code']==2){
					foreach($attendance_data as $att){
						if($att['timetable_id']==$vo['target']){
							$i++;
							$assign_data[$date][$i]['title'] = '班级：'.$att['class_name'].'</br>教室：'.$att['classroom_name'].'</br>第'.$att['times'].'/'.$att['class_number'].'次课';
							$assign_data[$date][$i]['startTime'] = date('Y年n月j日 H:i',$att['start_time']);
							$assign_data[$date][$i]['endTime'] = date('H:i',$att['end_time']);
							$assign_data[$date][$i]['timetable_id'] = $att['timetable_id'];
							$assign_data[$date][$i]['teacher'] = $att['teacher'];
							$assign_data[$date][$i]['code'] = $att['code'];
						}
					}
				}
			}
			$this->assign('timetable_data',json_encode($assign_data));
			// 获取课表结束
			$this->assign('student',$student);
			$this->assign('sign_data',$sign_data);
			return $this->fetch();
		}
		else{
			$this->error('非法访问！');
		}
	}
	
	public function attendance(){
		if($this->request->param()){
			$param = $this->request->param();
			switch($param['code']){
				case -3:
				case -2:
				case -1:
				case 0:
				case 1:
				case 3:
					//录入操作信息
					$data['code'] = $param['code'];
					$data['edit_id'] = cmf_get_current_admin_id();
					$data['last_edit_time'] = time();
					$is_attendance = db('student_attendance')->where(['student_id'=>$param['student_id'],'timetable_id'=>$param['timetable_id']])->find();
					if($is_attendance){
						if($is_attendance['code']==2){
							$update_data['more'] = json_encode(['attendance_status'=>$param['code']]);
							$update_data['edit_id'] = cmf_get_current_admin_id();
							$update_data['last_edit_time'] = time();
							if(db('student_attendance')->where(['id'=>$is_attendance['id']])->update($update_data)){
								$this->success('操作成功！');
							}
							else{
								$this->error('操作失败！');
							}
						}
						else{
							$data['status'] = 1;
							$data['target'] = '';
							if(db('student_attendance')->where(['id'=>$is_attendance['id']])->update($data)){
								$this->success('操作成功！');
							}
							else{
								$this->error('操作失败！');
							}
						}
					}
					else{
						$data['status'] = 1;
						$data['target'] = '';
						$data['more'] = '';
						$data['student_id'] = $param['student_id'];
						$data['timetable_id'] = $param['timetable_id'];
						if(db('student_attendance')->insert($data)){
							$this->success('操作成功！');
						}
						else{
							$this->error('操作失败！');
						}
					}
					break;
				case 2:
					//调班操作
					$data['code'] = $param['code'];
					$data['edit_id'] = cmf_get_current_admin_id();
					$data['last_edit_time'] = time();
					$is_attendance = db('student_attendance')->where(['student_id'=>$param['student_id'],'timetable_id'=>$param['timetable_id']])->find();
					if(db('student_attendance')->where(['student_id'=>$param['student_id'],'target'=>$param['target']])->find()){
						$this->error('不可多次调入同一节课！');
					}
					if($is_attendance){
						$data['status'] = 1;
						$data['target'] = $param['target'];
						if(db('student_attendance')->where(['id'=>$is_attendance['id']])->update($data)){
							$this->success('调课操作成功！');
						}
						else{
							$this->error('调课操作失败！');
						}
					}
					else{
						$data['status'] = 1;
						$data['target'] = $param['target'];
						$data['more'] = '';
						$data['student_id'] = $param['student_id'];
						$data['timetable_id'] = $param['timetable_id'];
						if(db('student_attendance')->insert($data)){
							$this->success('调课操作成功！');
						}
						else{
							$this->error('调课操作失败！');
						}
					}
					break;
				default:
					$this->error('非法访问！');
			}
		}
		else{
			$this->error('非法访问！');
		}
	}
}
